package com.demo.dao;

import java.io.Serializable;
import java.util.Date;
import java.util.List;
import java.util.Optional;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import com.demo.entity.User;

public interface UserDAO extends JpaRepository<User,Long>,Serializable {
	
	
		//where id in(100,300,400)
		//findBy开头的方法,根据方法名查询,JPQL风格的方法
		public List<User> findBySex(String a);//where sex='男'
		public List<User> findByUsernameAndSex(String username,String sex);//查询用户名和性别等于给定值
		public List<User> findByUsernameOrSex(String username,String sex);//查询用户名或性别等于给定值
		public List<User> findByHeightBetween(Integer height, Integer height2);//where height between height and height2
		public List<User> findByHeightLessThan(Integer height);//查询身高小于给定值 //where height<160
		public List<User> findByHeightLessThanEqual(Integer height);//查询身高小于等于给定值 //where height<=160
		public List<User> findByBirthBetween(Date birth, Date birth2);
		public List<User> findByBirthLessThan(Date birth);//查询生日小于给定值
		//public List<User> findByHeightIsNull(Integer height);//身高为null  where height=Null
		//public List<User> findByHeightNotNull(Integer height);//身高不为null   where height!=Null
		public List<User> findByHeightIn(List<Integer> height);//相当于in子句   where id in (1,3,5,7,9)
		public List<User> findByHeightOrderByBirth(Integer height) ;//按身高查询，然后按生日升序输出
	    
		
	    //query注解可加可不加，前提是方法名是find..By..属性名存在于数据库
	    @Query(value = "select u from User u where u.username = ?1")  //hibernate hql的风格：操纵的是对象和属性，而不是sql的表和列
		public User findByUsername(String username);
	    
	    @Query(value = "select u from User u where u.username like %?1%")
		public List<User> findByUsernameLike(String username);
	    
		//也可以使用本地sql查询
		@Query(value = "select u.* from t_user u where u.username=?1", nativeQuery = true) //这条就是sql的语法，操纵的就是表和列
	    public List<User> findBySQL(String username);
		
		public Page<User> findByUsernameLike(String username, Pageable pageable);
		
		//项目中多条件   组合查询
}